// MDX Functions
export const FUNCTIONS = [
  {
    label: 'Aggregate',
    expression: `Aggregate(Set_Expression [ ,Numeric_Expression ])`,
    insertText: 'Aggregate()',
    documentation: `Returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.`
  },
  {
    label: 'Filter',
    expression: `Filter(**Set_Expression**, **Logical_Expression**)`,
    insertText: 'Filter()',
    documentation: `Returns the set that results from filtering a specified set based on a search condition.

The Filter function evaluates the specified logical expression against each tuple in the specified set. The function returns a set that consists of each tuple in the specified set where the logical expression evaluates to true. If no tuples evaluate to true, an empty set is returned.

The Filter function works in a fashion similar to that of the IIf function. The IIf function returns only one of two options based on the evaluation of an MDX logical expression, while the Filter function returns a set of tuples that meet the specified search condition. In effect, the Filter function executes \`IIf(Logical_Expression, Set_Expression.Current, NULL)\` on each tuple in the set, and returns the resulting set.

## Examples

The following example returns the sum of the \`Measures.[Order Quantity]\` member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

\`\`\`sql
WITH MEMBER [Date].[Calendar].[First8Months2003] AS  
    Aggregate(  
        PeriodsToDate(  
            [Date].[Calendar].[Calendar Year],   
            [Date].[Calendar].[Month].[August 2003]  
        )  
    )  
SELECT   
    [Date].[Calendar].[First8Months2003] ON COLUMNS,  
    [Product].[Category].Children ON ROWS  
FROM  
    [Adventure Works]  
WHERE  
    [Measures].[Order Quantity]
\`\`\`
`
  },
  {
    label: 'Avg',
    expression: 'Avg(Set_Expression [, Numeric_Expression])',
    insertText: 'Avg()',
    documentation:
      'Returns the average (arithmetic mean) of a numeric expression that is evaluated over the specified set.'
  },
  {
    label: 'Count',
    expression: 'Count(Set_Expression [, ExcludeEmpty | IncludeEmpty])',
    insertText: 'Count()',
    documentation:
      'Returns the number of tuples in a specified set, with an optional flag to include or exclude empty tuples.'
  },
  {
    label: 'Max',
    expression: 'Max(Set_Expression, Numeric_Expression)',
    insertText: 'Max()',
    documentation: 'Returns the maximum value of a numeric expression evaluated over a specified set.'
  },
  {
    label: 'Min',
    expression: 'Min(Set_Expression, Numeric_Expression)',
    insertText: 'Min()',
    documentation: 'Returns the minimum value of a numeric expression evaluated over a specified set.'
  },
  {
    label: 'Sum',
    expression: 'Sum(Set_Expression [, Numeric_Expression])',
    insertText: 'Sum()',
    documentation: 'Returns the sum of a numeric expression evaluated over a specified set.'
  },
  {
    label: 'Exists',
    expression: 'Exists(Set_Expression1, Set_Expression2 [, String_Expression])',
    insertText: 'Exists()',
    documentation:
      'Returns the set of tuples of the first set specified that exist with one or more tuples of the second specified set.'
  },
  {
    label: 'Generate',
    expression: 'Generate(Set_Expression, Expression [, ALL])',
    insertText: 'Generate()',
    documentation: 'Returns a set generated by evaluating a specified expression for each tuple in a specified set.'
  },
  {
    label: 'IIf',
    expression: 'IIf(Logical_Expression, Expression1, Expression2)',
    insertText: 'IIf()',
    documentation: 'Returns one of two expressions based on the evaluation of a logical expression.'
  },
  {
    label: 'CoalesceEmpty',
    expression: 'CoalesceEmpty(Expression1, Expression2)',
    insertText: 'CoalesceEmpty()',
    documentation: 'Returns the first expression if it is not empty; otherwise, returns the second expression.'
  },
  {
    label: 'CrossJoin',
    expression: 'CrossJoin(Set_Expression1, Set_Expression2)',
    insertText: 'CrossJoin()',
    documentation: 'Returns the cross product of two sets.'
  },
  {
    label: 'ClosingPeriod',
    expression: 'ClosingPeriod([<Level>][, <Member>])',
    insertText: 'ClosingPeriod()',
    documentation: 'Returns the last descendant of a member at a level.'
  },
  {
    label: 'Children',
    expression: '<Member>.Children',
    insertText: '.Children',
    documentation: 'Returns the children of a member.'
  },
  
  {
    label: 'Except',
    expression: 'Except(Set_Expression1, Set_Expression2 [, ALL])',
    insertText: 'Except()',
    documentation:
      'Returns the set that results from removing the tuples of the second set specified from the first set specified.'
  },
  {
    label: 'Intersect',
    expression: 'Intersect(Set_Expression1, Set_Expression2 [, ALL])',
    insertText: 'Intersect()',
    documentation: 'Returns the intersection of two sets, optionally retaining duplicates.'
  },
  {
    label: 'Union',
    expression: 'Union(Set_Expression1, Set_Expression2 [, ALL])',
    insertText: 'Union()',
    documentation: 'Returns the union of two sets, optionally retaining duplicates.'
  },
  {
    label: 'FirstChild',
    expression: 'Member_Expression.FirstChild',
    insertText: '.FirstChild',
    documentation: 'Returns the first child of a specified member.'
  },
  {
    label: 'LastChild',
    expression: 'Member_Expression.LastChild',
    insertText: '.LastChild',
    documentation: 'Returns the last child of a specified member.'
  },
  {
    label: 'FirstSibling',
    expression: 'Member_Expression.FirstSibling',
    insertText: '.FirstSibling',
    documentation: 'Returns the first sibling of a specified member.'
  },
  {
    label: 'LastSibling',
    expression: 'Member_Expression.LastSibling',
    insertText: '.LastSibling',
    documentation: 'Returns the last sibling of a specified member.'
  },
  {
    label: 'NextMember',
    expression: 'Member_Expression.NextMember',
    insertText: '.NextMember',
    documentation: 'Returns the next member in the level that contains a specified member.'
  },
  {
    label: 'PrevMember',
    expression: 'Member_Expression.PrevMember',
    insertText: '.PrevMember',
    documentation: 'Returns the previous member in the level that contains a specified member.'
  },
  {
    label: 'Ancestor',
    expression: 'Ancestor(Member_Expression, Level_Expression)',
    insertText: 'Ancestor()',
    documentation: 'Returns the ancestor of a member at a specified level.'
  },
  {
    label: 'Cousin',
    expression: 'Cousin(Member_Expression, Ancestor_Member_Expression)',
    insertText: 'Cousin()',
    documentation: 'Returns the cousin of a member based on a second member.'
  },
  {
    label: 'Parent',
    expression: 'Member_Expression.Parent',
    insertText: '.Parent',
    documentation: 'Returns the parent of a specified member.'
  },
  {
    label: 'StrToMember',
    expression: 'StrToMember(String_Expression [, CONSTRAINED])',
    insertText: 'StrToMember()',
    documentation:
      'Converts a string to a member. If the optional CONSTRAINED flag is used, the function will raise an error if the member does not exist.'
  },
  {
    label: 'StrToSet',
    expression: 'StrToSet(String_Expression [, CONSTRAINED])',
    insertText: 'StrToSet()',
    documentation:
      'Converts a string to a set. If the optional CONSTRAINED flag is used, the function will raise an error if the set does not exist.'
  },
  {
    label: 'StrToValue',
    expression: 'StrToValue(String_Expression)',
    insertText: 'StrToValue()',
    documentation: 'Converts a string to a value.'
  },
  {
    label: 'CurrentMember',
    expression: 'Hierarchy_Expression.CurrentMember',
    insertText: '.CurrentMember',
    documentation: 'Returns the current member along a specified hierarchy during iteration.'
  },
  {
    label: 'DefaultMember',
    expression: 'Hierarchy_Expression.DefaultMember',
    insertText: '.DefaultMember',
    documentation: 'Returns the default member of a specified hierarchy.'
  },
  {
    label: 'Members',
    expression: 'Level_Expression.Members',
    insertText: '.Members',
    documentation: 'Returns the set of members in a specified level.'
  },
  {
    label: 'AllMembers',
    expression: 'Hierarchy_Expression.AllMembers',
    insertText: '.AllMembers',
    documentation: 'Returns the set of all members, including calculated members, in a specified hierarchy.'
  },
  {
    label: 'Ascendants',
    expression: '<Set> Ascendants(<Member>)',
    insertText: '.Ascendants',
    documentation: 'Returns the set of the ascendants of a specified member.'
  },
  
  {
    label: 'Descendants',
    expression: 'Descendants(Member_Expression, Level_Expression [, Desc_Flag])',
    insertText: 'Descendants()',
    documentation:
      'Returns the set of descendants of a specified member at a specified level, optionally filtered by a specified type of descendant.'
  },
  {
    label: 'DrilldownLevel',
    expression: 'DrilldownLevel(Set_Expression [, Level_Expression [, RECURSIVE]])',
    insertText: 'DrilldownLevel()',
    documentation:
      'Returns a set generated by drilling down a specified number of levels from a specified level, optionally drilling down recursively.'
  },
  {
    label: 'DrilldownMember',
    expression: 'DrilldownMember(Set_Expression, Set_Expression2 [, RECURSIVE])',
    insertText: 'DrilldownMember()',
    documentation:
      'Returns a set generated by drilling down from a specified member, optionally drilling down recursively.'
  },
  {
    label: 'DrillupLevel',
    expression: 'DrillupLevel(Set_Expression [, Level_Expression])',
    insertText: 'DrillupLevel()',
    documentation: 'Returns a set generated by drilling up from a specified level to its parent level.'
  },
  {
    label: 'DrillupMember',
    expression: 'DrillupMember(Set_Expression, Set_Expression2)',
    insertText: 'DrillupMember()',
    documentation: 'Returns a set generated by drilling up from a specified member to its parent member.'
  },

  {
    label: 'BottomCount',
    expression: 'BottomCount(Set_Expression, Count [, Numeric_Expression])',
    insertText: 'BottomCount()',
    documentation: 'Returns the bottom N tuples of a specified set, optionally ordered by a numeric expression.'
  },
  
  {
    label: 'BottomPercent',
    expression: 'BottomPercent(Set_Expression, Percent [, Numeric_Expression])',
    insertText: 'BottomPercent()',
    documentation:
      'Returns the bottom N percent of tuples in a specified set, optionally ordered by a numeric expression.'
  },
  {
    label: 'BottomSum',
    expression: '<Set> BottomSum(<Set>, <Numeric Expression>, <Numeric Expression>)',
    insertText: 'BottomSum()',
    documentation:
      'Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value.'
  },
  {
    label: 'TopCount',
    expression: 'TopCount(Set_Expression, Count [, Numeric_Expression])',
    insertText: 'TopCount()',
    documentation: 'Returns the top N tuples of a specified set, optionally ordered by a numeric expression.'
  },
  {
    label: 'TopPercent',
    expression: 'TopPercent(Set_Expression, Percent [, Numeric_Expression])',
    insertText: 'TopPercent()',
    documentation: `Returns the top N percent of tuples in a specified set, optionally ordered by a numeric expression.
  
The TopPercent function calculates the sum of the specified numeric expression evaluated over the specified set, sorting the set in descending order.
The function then returns the elements with the highest values whose cumulative percentage of the total summed value is at least the specified percentage.
This function returns the smallest subset of a set whose cumulative total is at least the specified percentage. The returned elements are ordered largest to smallest.`
  },
  {
    label: 'TopSum',
    expression: '<Set> TopSum(<Set>, <Numeric Expression>, <Numeric Expression>)',
    insertText: 'TopSum()',
    documentation:
      'Sorts a set and returns the top N elements whose cumulative total is at least a specified value.'
  },
  {
    label: 'Head',
    expression: 'Head(Set_Expression, Count)',
    insertText: 'Head()',
    documentation: 'Returns the first N elements of a specified set.'
  },
  {
    label: 'Tail',
    expression: 'Tail(Set_Expression, Count)',
    insertText: 'Tail()',
    documentation: 'Returns the last N elements of a specified set.'
  },
  {
    label: 'Item',
    expression: 'Set_Expression.Item(Index)',
    insertText: '.Item()',
    documentation: 'Returns the tuple at a specified index within a set.'
  },
  {
    label: 'Left',
    expression: 'Left(Member_Expression | String, Count)',
    insertText: 'Left()',
    documentation: 'Returns a specified number of characters from the left side of a string.'
  },
  {
    label: 'Len',
    expression: 'Len(Member_Expression | String)',
    insertText: 'Len()',
    documentation: 'Returns the number of characters in a string.'
  },

  {
    label: 'Order',
    expression: 'Order(Set_Expression, Numeric_Expression [, ASC | DESC | BASC | BDESC])',
    insertText: 'Order()',
    documentation: 'Returns a set sorted by a specified numeric expression, optionally specifying the sort order.'
  },
  {
    label: 'Rank',
    expression: 'Rank(Tuple_Expression, Set_Expression [, Numeric_Expression])',
    insertText: 'Rank()',
    documentation:
      'Returns the one-based rank of a specified tuple within a specified set, optionally ordered by a numeric expression.'
  },
  {
    label: 'ParallelPeriod',
    expression: 'ParallelPeriod(<Level>[, <Numeric Expression>[, <Member>]])',
    insertText: 'ParallelPeriod()',
    documentation:
      'Returns a member from a prior period in the same relative position as a specified member.'
  },
  {
    label: 'PeriodsToDate',
    expression: 'PeriodsToDate(<Level>, <Member>)',
    insertText: 'PeriodsToDate()',
    documentation:
      'Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.'
  },

  {
    label: 'Properties',
    expression: 'Member_Expression.Properties(Property_Name [, TYPED])',
    insertText: '.Properties()',
    documentation:
      'Returns the value of a specified property for a specified member, optionally returning the value in the data type of the property.'
  },
  {
    label: 'IsEmpty',
    expression: 'IsEmpty(Expression)',
    insertText: 'IsEmpty()',
    documentation: 'Returns true if a specified expression is empty; otherwise, returns false.'
  },
  {
    label: 'IsLeaf',
    expression: 'IsLeaf(Expression)',
    insertText: 'IsLeaf()',
    documentation: 'Returns true if a specified member is a leaf member (has no children); otherwise, returns false.'
  },
  {
    label: 'IsSibling',
    expression: 'IsSibling(Member_Expression1, Member_Expression2)',
    insertText: 'IsSibling()',
    documentation:
      'Returns true if the specified members are siblings (share the same parent); otherwise, returns false.'
  }
]
